[Amazon Athena] WHERE句で期間を指定して日付カラムでデータを絞り込む

こんにちは、CX事業本部 IoT事業部の若槻です。

Amazon Athenaで日付カラムを持つデータを期間指定で取得する方法を確認していたところ、同じ取得でも何通りか方法があり迷うことがありました。

そこで今回は、WHERE句で期間を指定して日付カラムでデータを絞り込む方法をまとめてみました。

やってみた

環境構築

AWS CDK v2（TypeScript）で次のようなCDKスタックを作成します。

import { Construct } from 'constructs';
import {
  aws_s3,
  aws_athena,
  RemovalPolicy,
  Stack,
  StackProps,
} from 'aws-cdk-lib';
import * as glue from '@aws-cdk/aws-glue-alpha';

export class ProcessStack extends Stack {
  constructor(scope: Construct, id: string, props: StackProps) {
    super(scope, id, props);

    // ソースデータ格納バケット
    const sourceDataBucket = new aws_s3.Bucket(this, 'sourceDataBucket', {
      bucketName: `data-${this.account}`,
      removalPolicy: RemovalPolicy.DESTROY,
    });

    // Athenaクエリ結果格納バケット
    const athenaQueryResultBucket = new aws_s3.Bucket(
      this,
      'athenaQueryResultBucket',
      {
        bucketName: `athena-query-result-${this.account}`,
        removalPolicy: RemovalPolicy.DESTROY,
      },
    );

    // データカタログ
    const dataCatalog = new glue.Database(this, 'dataCatalog', {
      databaseName: 'data_catalog',
    });

    // データカタログテーブル
    const sourceDataGlueTable = new glue.Table(this, 'sourceDataGlueTable', {
      tableName: 'source_data_glue_table',
      database: dataCatalog,
      bucket: sourceDataBucket,
      s3Prefix: 'data/',
      partitionKeys: [
        {
          name: 'dt',
          type: glue.Schema.STRING,
        },
      ],
      dataFormat: glue.DataFormat.JSON,
      columns: [
        {
          name: 'userId',
          type: glue.Schema.STRING,
        },
        {
          name: 'count',
          type: glue.Schema.INTEGER,
        },
      ],
    });

    // データカタログテーブルへのPartition Projectionの設定
    // eslint-disable-next-line @typescript-eslint/no-explicit-any
    (sourceDataGlueTable.node.defaultChild as any).tableInput.parameters = {
      'projection.enabled': true,
      'projection.dt.type': 'date',
      'projection.dt.range': '2022/06/28,NOW',
      'projection.dt.format': 'yyyy/MM/dd',
      'projection.dt.interval': 1,
      'projection.dt.interval.unit': 'DAYS',
      'storage.location.template':
        `s3://${sourceDataBucket.bucketName}/data/` + '${dt}',
    };

    // Athenaワークグループ
    new aws_athena.CfnWorkGroup(
      this,
      'athenaWorkGroup',
      {
        name: 'athenaWorkGroup',
        workGroupConfiguration: {
          resultConfiguration: {
            outputLocation: `s3://${athenaQueryResultBucket.bucketName}/result-data`,
          },
        },
      },
    );
  }
}

• yyyy/MM/ddというフォーマットのdate型のカラムを持ったデータをクエリ対象とします。

上記をCDK Deployしてスタックをデプロイします。

日付カラムでデータを絞り込む

絞り込みを行わない場合

SELECT *
FROM "data_catalog"."source_data_glue_table"

すべての日付のデータを取得できます。

等号で絞り込む

1つの日付なので期間とは言えないですが、等号（=）を使用できます。

SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE dt = '2022/06/29'

不等号で絞り込む

SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE dt <= '2022/06/29'

2022/06/29以前のデータが絞り込めました。

ちなみに不等号は2つ以上を1度には使えません。

#エラーになる
SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE '2022/06/28' < dt <= '2022/06/29'

2つ以上使いたい場合はAND句やOR句を使う必要があります。

SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE '2022/06/28' < dt AND dt <= '2022/06/29'

BETWEEN句で絞り込む

BETWEEN句で2つの日付の間のデータを絞り込むことができます。その際にdate_parse()やdateでdate型を指定することがポイントです。

SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE date_parse(dt, '%Y/%m/%d') BETWEEN date '2022-06-28' AND date '2022-06-29'

2022/06/28から2022/06/29までのデータが絞り込めました。

動的な日付や差分を指定する

CURRENT_TIMESTAMPを使用すると現在時刻を取得できます。またINTERVALで日時の差分を取得できます。

SELECT
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP - INTERVAL '7' DAY

上記を利用して今日から1週間前までのデータを動的に取得するクエリも作れます。

SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE date_parse(dt, '%Y/%m/%d') BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP

タイムゾーンを指定する

CURRENT_TIMESTAMPで取得できる日付のタイムゾーンは既定で世界標準時（UTC）です。AT TIME ZONE '<Time Zone>'を使用してタイムゾーンを指定することも可能です。

SELECT
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo'

9時間ずれた日時が取得できています。

FORMAT_DATETIMEでdate型の値を指定の形式の文字列にフォーマットできます。これによりタイムゾーンを設定した日付を不等号でも使用できます。

SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE dt <= FORMAT_DATETIME(
		CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo',
		'YYYY/MM/dd'
	)

その他の方法

Amazon AthenaはPrestoエンジンを使用しているので、基本的にはPrestoのドキュメントが参考になります。他の方法を知りたい場合は読んでみましょう。

• Date and Time Functions and Operators — Presto 0.273.3 Documentation

参考

• Date and Time Functions and Operators — Presto 0.273.3 Documentation

以上